Arbeitspaket (AP) 3: Management & Nutzung Räumliche Daten¶
Angaben Studierende(r) (fehlende Angaben ergänzen)¶
| Vorname: | Mauricio |
| Nachname: | Saez |
| Immatrikulationsnummer: | 12642112 |
| Modul: | Data Science |
| Prüfungsdatum / Raum / Zeit: | 07.10.2024 / Raum: SF O3.54 / 8:00 – 11:45 |
| Erlaubte Hilfsmittel: | w.MA.XX.DS.24HS (Data Science) Open Book, Eigener Computer, Internet-Zugang |
| Nicht erlaubt: | Nicht erlaubt ist der Einsatz beliebiger Formen von generativer KI (z.B. Copilot, ChatGPT) sowie beliebige Formen von Kommunikation oder Kollaboration mit anderen Menschen. |
Bewertungskriterien¶
(max. erreichbare Punkte: 48)¶
| Kategorie | Beschreibung | Punkteverteilung |
|---|---|---|
| Code nicht lauffähig oder Ergebnisse nicht sinnvoll | Der Code enthält Fehler, die verhindern, dass er ausgeführt werden kann (z.B. Syntaxfehler) oder es werden Ergebnisse ausgegeben, welche nicht zur Fragestellung passen. | 0 Punkte |
| Code lauffähig, aber mit gravierenden Mängeln | Der Code läuft, aber die Ergebnisse sind aufgrund wesentlicher Fehler unvollständig (z.B. fehlende Joins, gravierende Fehler in SQL-Abfragen). Nur geringer Fortschritt erkennbar. | 25% der max. erreichbaren Punkte |
| Code lauffähig, aber mit mittleren Mängeln | Der Code läuft und liefert teilweise korrekte Ergebnisse, aber es gibt grössere Fehler (z.B. fehlende Spalten, unvollständige SQL-Abfragen). Die Ergebnisse sind nachvollziehbar, aber unvollständig oder ungenau. | 50% der max. erreichbaren Punkte |
| Code lauffähig, aber mit minimalen Mängeln | Der Code läuft und liefert ein weitgehend korrektes Ergebnis, aber kleinere Fehler (z.B. falsche oder fehlende Sortierung, Rundung von Werten falsch) beeinträchtigen die Vollständigkeit des Ergebnisses. | 75% der max. erreichbaren Punkte |
| Code lauffähig und korrekt | Der Code läuft einwandfrei und liefert das korrekte Ergebnis ohne Mängel. | 100% der max. erreichbaren Punkte |
Python Libraries und Settings¶
In [58]:
# Libraries
import os
import folium
import pandas as pd
import geopandas as gpd
from sqlalchemy import create_engine, text
# Ignore warnings
import warnings
warnings.filterwarnings("ignore")
print(os.getcwd())
/workspaces/python_postgresql_postgis
Vorbereitung (Hinweis: dieser Teil wird nicht bewertet)¶
1.) Starten Sie eine GitHub Codespaces Instanz auf Basis Ihres Forks des folgenden GitHub Repositories:¶
GitHub-Repository: https://github.com/mario-gellrich-zhaw/python_postgresql_postgis¶
WICHTIG!!! Verwenden Sie eine GitHub Codespaces Instanz mit ausreichend Arbeitsspeicher (4core, 16GB RAM).¶
Hinweis:
- Im Unterricht wurden bereits sämtliche Installationen und Einstellungen inkl. der Registrierung des Datenbank Servers auf pgAdmin vorgenommen.
- Falls Sie die Codespaces-Instanz neu erstellen müssen, folgen Sie bitte den detaillierten Erklärungen auf der README-Seite des GitHub Repositories.
2.) Erstellen und Testen Sie die Datenbankverbindung mit der 'osm_switzerland' Datenbank.¶
In [59]:
# Set up Database Connection
user = "pgadmin"
password = "geheim"
host = "localhost"
port = "5432"
database = "osm_switzerland"
# Erstellen der Connection URL
db_connection_url = "postgresql://" + user + ":" + password +\
"@" + host + ":" + port + "/" + database
# Erstellen SQLAlchemy Engine
engine = create_engine(db_connection_url)
# Test der Connection
with engine.connect() as connection:
result = connection.execute(text('SELECT current_database()'))
print(result.fetchone())
# Verbindung trennen
engine.dispose()
('osm_switzerland',)
Aufgaben (Dieser Teil wird bewertet!)¶
Hinweise zu den folgenden Aufgabenstellungen:
- In diesem Jupyter Notebook gibt es jeweils zwei Code-Zellen pro Aufgabe:
- Eine Codezelle mit Python-Code und einem SQL-Statement für die Datenbank-Abfrage.
- Eine Codezelle mit Python-Code für die Kartendarstellung der Ergebnisse der jeweiligen SQL-Abfrage.
- In den Codezellen für die Datenbank-Abfrage muss jeweils das SQL-Statement ergänzt werden.
- In den Codezellen für die Kartendarstellung muss nur dann der Python Code ergänzt werden, wenn in der Aufgabe danach gefragt wird.
Aufgabe (1): Erstellen Sie eine Abfrage sämtlicher Autoreparatur-Werkstätten in der Schweiz¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Daten in der Tabelle 'planet_osm_point'.
- Stellen sie in der Ergebnistabelle die Spalten: osm_id, shop sowie die transformierte Geometrie als Spalte geom dar.
- Tipp: Die Geometry wird mit Hilfe der Funktion st_transform() transformiert, z.B.: st_transform(p.way, 4326) AS geom.
- Tipp: Autoreparatur-Werkstätten sind mit dem key:value Paar shop='car_repair' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 6)
In [60]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
h.osm_id,
h.shop,
h.name,
ST_Transform(h.way, 4326) AS geom
FROM planet_osm_point h
WHERE h.shop = 'car_repair';"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[60]:
| osm_id | shop | name | geom | |
|---|---|---|---|---|
| 0 | 1811755810 | car_repair | Grenzgarage | POINT (9.62898 47.45412) |
| 1 | 9408250312 | car_repair | Gebr. Wirth AG | POINT (9.63098 47.45327) |
| 2 | 2539306181 | car_repair | Lantech | POINT (9.58444 47.46663) |
| 3 | 2530851973 | car_repair | Garage Welpe | POINT (9.58777 47.46975) |
| 4 | 3346119599 | car_repair | Garage Martino GmbH | POINT (9.49096 47.47737) |
| ... | ... | ... | ... | ... |
| 1459 | 4386729493 | car_repair | Garage Stahel | POINT (9.26639 47.55844) |
| 1460 | 4366881989 | car_repair | Autoviva VW und Seat | POINT (9.27967 47.54715) |
| 1461 | 6092408245 | car_repair | Nussberger Direktimport | POINT (9.34644 47.53412) |
| 1462 | 1492440172 | car_repair | Engeler Automobile | POINT (9.20147 47.63996) |
| 1463 | 1493634725 | car_repair | Morandi Group AG | POINT (9.19848 47.63967) |
1464 rows × 4 columns
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [61]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=8,
tiles='CartoDB positron')
# Map settings
folium.GeoJson(
gdf,
name='map'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[61]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (2) Erstellen Sie eine Abfrage aller Biergärten in der Schweiz.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Daten in den Tabellen 'planet_osm_point'.
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, amenity, name und die transformierte Geometrie als Spalte geom dar.
- Tipp: Biergärten sind mit dem key:value Paar amenity='biergarten' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 6)
In [62]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
h.osm_id,
h.amenity,
h.name,
ST_Transform(h.way, 4326) AS geom
FROM planet_osm_point h
WHERE h.amenity = 'biergarten';"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf.head()
Out[62]:
| osm_id | amenity | name | geom | |
|---|---|---|---|---|
| 0 | 704467869 | biergarten | Bierhalle | POINT (9.6068 47.40694) |
| 1 | 423833242 | biergarten | Brasserie-Bar de la Poste | POINT (6.93199 46.99151) |
| 2 | 746772927 | biergarten | Buvette de l'alpage du col du Lein | POINT (7.15997 46.11045) |
| 3 | 370416485 | biergarten | Bar des Etablons | POINT (7.23621 46.14038) |
| 4 | 6109596184 | biergarten | Dolce Vita Gelati Bar | POINT (7.6897 46.68853) |
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [63]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=8,
tiles='CartoDB positron')
# Map settings
folium.GeoJson(
gdf,
name='map'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[63]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (3): Erstellen Sie eine Abfrage aller Gebäude in der Stadthausstrasse in Winterthur, welche vollständige Adressangaben besitzen.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Daten in der Tabelle 'planet_osm_polygon'.
- Vollständige Adressangabe bedeutet: Strassenname, Haunummer, PLZ, Gemeindename sind vorhanden.
- Stellen Sie in der Ergebnistabelle sämtliche Adressangaben sowie die transformierte Geometrie als Spalte geom dar.
- Verwenden Sie für die Darstellung als Hintergrundkarte ein Satellitenbild (ESRIWorldImagery) als maptile.
- Tipp: Gebäude sind in der Spalte 'building' klassifiziert. Mit WHERE building IS NOT NULL können Sie Gebäude filtern.
(max. erreichbare Punkte: 6)
In [64]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
p.osm_id,
p."addr:street",
p."addr:housenumber",
p."addr:city",
p."addr:postcode",
p.building,
st_transform(p.way, 4326) AS geom
FROM
public.planet_osm_polygon AS p
WHERE
p."addr:street" IS NOT NULL
AND p."addr:housenumber" IS NOT NULL
AND p."building" IS NOT NULL
AND p."addr:city"='Winterthur'
AND p."addr:street"='Stadthausstrasse'
"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[64]:
| osm_id | addr:street | addr:housenumber | addr:city | addr:postcode | building | geom | |
|---|---|---|---|---|---|---|---|
| 0 | 75027485 | Stadthausstrasse | 31 | Winterthur | 8400 | office | POLYGON ((8.73162 47.50041, 8.73165 47.50029, ... |
| 1 | 75027472 | Stadthausstrasse | 35 | Winterthur | 8400 | apartments | POLYGON ((8.73153 47.5004, 8.73157 47.50026, 8... |
| 2 | 75027503 | Stadthausstrasse | 37 | Winterthur | 8400 | apartments | POLYGON ((8.73146 47.5004, 8.73149 47.50028, 8... |
| 3 | 75027432 | Stadthausstrasse | 39 | Winterthur | 8400 | office | POLYGON ((8.73111 47.50039, 8.73115 47.50025, ... |
| 4 | 230520600 | Stadthausstrasse | 61 | Winterthur | 8400 | office | POLYGON ((8.72952 47.5002, 8.72955 47.50006, 8... |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 56 | 24804763 | Stadthausstrasse | 22 | Winterthur | 8400 | office | POLYGON ((8.72448 47.49982, 8.72453 47.4997, 8... |
| 57 | 26992511 | Stadthausstrasse | 24 | Winterthur | 8400 | retail | POLYGON ((8.72405 47.49972, 8.72407 47.49967, ... |
| 58 | 134980581 | Stadthausstrasse | 10b | Winterthur | 8400 | yes | POLYGON ((8.72652 47.50075, 8.72661 47.50063, ... |
| 59 | 9264543 | Stadthausstrasse | 4a | Winterthur | 8400 | government | POLYGON ((8.73111 47.50115, 8.73112 47.50085, ... |
| 60 | 22301937 | Stadthausstrasse | 4 | Winterthur | 8400 | apartments | POLYGON ((8.73232 47.50109, 8.73233 47.50107, ... |
61 rows × 7 columns
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [65]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=17,
tiles='ESRIWorldImagery')
# Map settings
folium.Choropleth(
geo_data=gdf,
name='map',
fill_color='greenyellow'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[65]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (4): Erstellen Sie eine Abfrage aller Strassen in der Schweiz, welche als 'motorway' klassifiziert sind.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Informationen in der Tabelle 'planet_osm_roads'.
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, highway und die transformierte Geometrie als Spalte geom dar.
- Tipp: Motorways sind mit dem key:value Paar highway='motorway' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 6)
In [66]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """-- Create buffer around major roads
SELECT
1 as group_id,
ST_TRANSFORM(p.way::geometry, 4326) AS geom
FROM public.planet_osm_roads AS p
WHERE
highway = 'motorway';"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[66]:
| group_id | geom | |
|---|---|---|
| 0 | 1 | LINESTRING (9.64218 47.43433, 9.6422 47.43402,... |
| 1 | 1 | LINESTRING (9.64232 47.43412, 9.6423 47.43469) |
| 2 | 1 | LINESTRING (9.6423 47.43469, 9.64232 47.43504,... |
| 3 | 1 | LINESTRING (9.64312 47.43793, 9.6429 47.43753,... |
| 4 | 1 | LINESTRING (9.64235 47.43533, 9.6424 47.43568,... |
| ... | ... | ... |
| 8263 | 1 | LINESTRING (9.15834 47.65468, 9.15789 47.65424... |
| 8264 | 1 | LINESTRING (9.15371 47.65075, 9.15343 47.6503,... |
| 8265 | 1 | LINESTRING (9.14594 47.63688, 9.14599 47.63692... |
| 8266 | 1 | LINESTRING (9.14357 47.63107, 9.14364 47.63151) |
| 8267 | 1 | LINESTRING (9.14365 47.63224, 9.14352 47.63152) |
8268 rows × 2 columns
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [67]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=9,
tiles='CartoDB positron')
# Map settings
folium.Choropleth(
geo_data=gdf,
name='map',
line_weight=3,
line_color='red'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[67]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (5): Erstellen Sie eine Abfrage aller Schweizer Flüsse. Generieren Sie zusätzlich Buffer um die Flüsse mit einer Breite von 2000m.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Informationen in der Tabelle 'planet_osm_line'.
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, waterway sowie die transformierte Geometrie als Spalte geom dar.
- Tipp: Flüsse sind mit dem key:value Paar waterway='river' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
- Tipp: Per Default wird für jedes Fluss-Segment ein separater Buffer erstellt. Es ist nicht notwendig daraus einen einzelnen Buffer zu generieren.
(max. erreichbare Punkte: 8)
In [68]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """-- Create buffer around major rivers
SELECT
1 as group_id,
ST_TRANSFORM(ST_UNION(ST_Buffer(p.way::geometry, 2000)), 4326) AS geom
FROM public.planet_osm_line AS p
WHERE
waterway = 'river'
;"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[68]:
| group_id | geom | |
|---|---|---|
| 0 | 1 | MULTIPOLYGON (((5.92959 46.11614, 5.92771 46.1... |
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [69]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=8,
tiles='CartoDB positron')
# Map settings
folium.Choropleth(
geo_data=gdf,
name='map',
fill_color='greenyellow'
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[69]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (6): Erstellen Sie eine Abfrage der Bäckerei-Geschäfte in Zürich und Winterthur.¶
Details zur Aufgabenstellung:
- Sie finden die benötigten Daten in den Tabellen 'planet_osm_point' (Backereien).
- Verwenden Sie die Städtenamen aus den Adressangaben für die Abfrage der Bäckerei-Standorte (Zürich, Winterthur).
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, shop, name, "addr:city" sowie die transformierte Geometrie als geom dar.
- Wählen Sie eine Satelliten Karte von ESRI als Hintergrundkarte (maptile).
- Sortieren Sie die Bäckerei-Geschäfte aufsteigend nach osm_id.
- Tipp: Bäckerei-Geschäfte sind mit dem key:value Paar shop='bakery' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 8)
In [70]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
h.osm_id,
h.shop,
h.name,
h."addr:city",
ST_Transform(h.way, 4326) AS geom
FROM planet_osm_point h
WHERE h.shop = 'bakery'
AND h."addr:city" IN ('Winterthur','Zürich')
ORDER BY h.osm_id ASC
;"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf
Out[70]:
| osm_id | shop | name | addr:city | geom | |
|---|---|---|---|---|---|
| 0 | 252457457 | bakery | Steiner Flughafebeck | Zürich | POINT (8.49779 47.40311) |
| 1 | 266630770 | bakery | Brezelkönig | Zürich | POINT (8.48875 47.39147) |
| 2 | 267879346 | bakery | Walter Buchmann | Zürich | POINT (8.51892 47.36239) |
| 3 | 268602152 | bakery | Moser's | Zürich | POINT (8.54945 47.3632) |
| 4 | 270794699 | bakery | John Baker | Zürich | POINT (8.56645 47.36493) |
| ... | ... | ... | ... | ... | ... |
| 101 | 10082330824 | bakery | RAM3 | Winterthur | POINT (8.74193 47.49395) |
| 102 | 10884168277 | bakery | Juliette | Zürich | POINT (8.5328 47.36658) |
| 103 | 10946043353 | bakery | Babu's Bakery | Zürich | POINT (8.51568 47.37473) |
| 104 | 10955852823 | bakery | Täglich Brot | Zürich | POINT (8.50705 47.36079) |
| 105 | 11951988149 | bakery | Wagner | Zürich | POINT (8.51763 47.3698) |
106 rows × 5 columns
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [71]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=11,
tiles='EsriWorldImagery')
# Map settings
folium.GeoJson(
gdf,
name='map',
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[71]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Aufgabe (7): Erstellen Sie eine Abfrage sämtlicher Coiffeur-Geschäfte in einem Radius von 500m um den Hauptbahnhof in Zürich.¶
Details zur Aufgabenstellung:
- Sie finden die Daten in der Tabelle 'planet_osm_point'.
- Berechnen Sie in der Abfrage die Distanz jedes Coiffeur-Geschäfts zum Hauptbahnhof in Metern als Spalte 'distance_meters'.
- Stellen Sie in der Ergebnistabelle die Spalten: osm_id, shop, name, distance_meters, sowie die transformierte Geometrie als geom dar.
- Wählen Sie eine Satelliten Karte von ESRI als Hintergrundkarte (maptile).
- Integrieren Sie in die Kartendarstellung den Namen (Spalte 'name') der Coiffeur-Geschäfte als Popup.
- Tipp: Coiffeur-Geschäfte sind mit dem key:value Paar shop='hairdresser' in der OpenStreetMap Map-Feature Übersicht angegeben.
- vgl: https://wiki.openstreetmap.org/wiki/Map_features
(max. erreichbare Punkte: 8)
In [72]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)
# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
p.osm_id,
p.shop,
p.name,
ST_Distance(
ST_Transform(p.way, 4326)::geography,
-- my coordinates
ST_SetSRID(ST_MakePoint(8.53936, 47.3781), 4326)::geography
) AS distance_meters,
ST_TRANSFORM(p.way, 4326) AS geom
FROM
planet_osm_point AS p
WHERE
p.shop = 'hairdresser'
AND ST_DWithin(
ST_Transform(p.way, 4326)::geography,
-- my coordinates
ST_SetSRID(ST_MakePoint(8.53936, 47.3781), 4326)::geography,
500
)
ORDER BY distance_meters;"""
# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)
# Datenbankverbindung trennen
engine.dispose()
# Zeigen des GeoDataFrames
gdf.head()
Out[72]:
| osm_id | shop | name | distance_meters | geom | |
|---|---|---|---|---|---|
| 0 | 4833061523 | hairdresser | André Joe Coiffure | 72.419054 | POINT (8.54016 47.37774) |
| 1 | 4424939218 | hairdresser | McCoiffure | 111.635316 | POINT (8.53791 47.37791) |
| 2 | 4244059289 | hairdresser | Art Coiffure Kaiser | 133.439602 | POINT (8.53788 47.37745) |
| 3 | 4424939145 | hairdresser | Saleh | 151.462092 | POINT (8.54071 47.37709) |
| 4 | 693318659 | hairdresser | Coiffeur Insieme | 168.219919 | POINT (8.53992 47.37664) |
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [73]:
# Projektion definieren (WGS84)
if gdf.crs is None:
gdf.set_crs(epsg=4326, inplace=True)
else:
pass
# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()
# Initialisieren der Map
m = folium.Map(location=[lat, lon],
zoom_start=16,
tiles='EsriWorldImagery')
# Map settings
folium.GeoJson(
gdf,
name='map',
popup=folium.GeoJsonPopup(fields=['name'])
).add_to(m)
folium.LayerControl().add_to(m)
# Plot map
m
Out[73]:
Make this Notebook Trusted to load map: File -> Trust Notebook
Jupyter notebook --footer info-- (please always provide this at the end of each notebook)¶
In [74]:
import os
import platform
import socket
from platform import python_version
from datetime import datetime
print('-----------------------------------')
print(os.name.upper())
print(platform.system(), '|', platform.release())
print('Datetime:', datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
print('Python Version:', python_version())
print('IP Address:', socket.gethostbyname(socket.gethostname()))
print('-----------------------------------')
----------------------------------- POSIX Linux | 6.5.0-1025-azure Datetime: 2024-10-07 08:55:46 Python Version: 3.12.1 IP Address: 127.0.0.1 -----------------------------------